﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using SiteServer.Plugin;
using Thingpower.SS.EduOnline.Model;

namespace Thingpower.SS.EduOnline.Provider
{
    public class EnrollmentDAO : BaseDAO
    {
        public const string TableName = "courseware_Enrollment";

        public static List<TableColumn> Columns => new List<TableColumn>
        {
            new TableColumn
            {
                AttributeName = nameof(EnrollmentInfo.Id),
                DataType = DataType.Integer
            },
            new TableColumn
            {
                AttributeName = nameof(EnrollmentInfo.CoursewareId),
                DataType = DataType.Integer
            },
            new TableColumn
            {
                AttributeName = nameof(EnrollmentInfo.UserId),
                DataType = DataType.Integer
            },
            new TableColumn
            {
                AttributeName = nameof(EnrollmentInfo.AddDate),
                DataType = DataType.DateTime
            },
            new TableColumn
            {
                AttributeName = nameof(EnrollmentInfo.CompleteDate),
                DataType = DataType.DateTime
            },
            new TableColumn
            {
                AttributeName = nameof(EnrollmentInfo.Status),
                DataType = DataType.Integer
            }
        };

        public static int Insert(EnrollmentInfo model)
        {
            string sqlString = $@"INSERT INTO {TableName}(
                {nameof(EnrollmentInfo.CoursewareId)},
                {nameof(EnrollmentInfo.UserId)},
                {nameof(EnrollmentInfo.AddDate)}
            ) VALUES(
                @{nameof(EnrollmentInfo.CoursewareId)},
                @{nameof(EnrollmentInfo.UserId)},
                @{nameof(EnrollmentInfo.AddDate)}
            )";

            var parameters = new[]
            {
                DataApi.GetParameter($"@{nameof(EnrollmentInfo.CoursewareId)}", model.CoursewareId),
                DataApi.GetParameter($"@{nameof(EnrollmentInfo.UserId)}", model.UserId),
                DataApi.GetParameter($"@{nameof(EnrollmentInfo.AddDate)}", model.AddDate)
            };

            return DataApi.ExecuteNonQueryAndReturnId(TableName, nameof(EnrollmentInfo.Id), ConnectionString, sqlString, parameters);
        }

        public static void Delete(int userId)
        {
            string sqlString =
                $"DELETE FROM {TableName} WHERE {nameof(EnrollmentInfo.UserId)} = @{nameof(EnrollmentInfo.UserId)}";

            var parameters = new[]
            {
                DataApi.GetParameter($"@{nameof(EnrollmentInfo.UserId)}", userId)
            };

            DataApi.ExecuteNonQuery(ConnectionString, sqlString, parameters);
        }

        public static EnrollmentInfo GetModel(int userId, int coursewareId)
        {
            EnrollmentInfo _model = null;

            string sqlString =
                $"SELECT * FROM {TableName} WHERE {nameof(EnrollmentInfo.UserId)} = {userId} AND {nameof(EnrollmentInfo.CoursewareId)} = {coursewareId}";

            using (var rdr = DataApi.ExecuteReader(ConnectionString, sqlString))
            {
                if (rdr.Read())
                {
                    _model = GetModel(rdr);
                }
                rdr.Close();
            }

            return _model;

        }

        public static List<EnrollmentInfo> GetModels(int userId = 0, int coursewareId = 0)
        {
            List<EnrollmentInfo> _list = new List<EnrollmentInfo>();

            string _queryExpression = "";

            if(userId != 0)
            {
                _queryExpression += $" AND {nameof(EnrollmentInfo.UserId)} = {userId}";
            }

            if(coursewareId != 0)
            {
                _queryExpression += $" AND {nameof(EnrollmentInfo.CoursewareId)} = {coursewareId}";
            }

            string sqlString =
                $"SELECT * FROM {TableName} WHERE 1 = 1" + _queryExpression;

            using (var rdr = DataApi.ExecuteReader(ConnectionString, sqlString))
            {
                while (rdr.Read())
                {
                    _list.Add(GetModel(rdr));
                }
                rdr.Close();
            }

            return _list;
        }

        private static EnrollmentInfo GetModel(IDataRecord rdr)
        {
            if (rdr == null) return null;

            var _model = new EnrollmentInfo();

            var i = 0;
            _model.Id = rdr.IsDBNull(i) ? 0 : rdr.GetInt32(i);
            i++;
            _model.CoursewareId = rdr.IsDBNull(i) ? 0 : rdr.GetInt32(i);
            i++;
            _model.UserId = rdr.IsDBNull(i) ? 0 : rdr.GetInt32(i);
            i++;
            _model.AddDate = rdr.IsDBNull(i) ? DateTime.Now : rdr.GetDateTime(i);
            i++;
            _model.CompleteDate = rdr.IsDBNull(i) ? DateTime.Now : rdr.GetDateTime(i);
            i++;
            _model.Status = rdr.IsDBNull(i) ? 0 : rdr.GetInt32(i);

            return _model;
        }
    }
}